The present invention relates to database management systems, and more particularly to accessing a database over the Internet.
With the growing popularity of the Internet, so is the demand for access to relational databases via the Internet. FIG. 1 illustrates a conventional network environment 10, which allows access to relational databases 18 over the Internet. One example of this conventional network environment 10 is the Net.Data(trademark) network environment (also known as the xe2x80x9cDB2(copyright) WWWxe2x80x9d network environment), developed by International Business Machines, Corp. The Net.Data network environment comprises web clients 12, a web server 14, a Net.Data gateway 16, and relational database management system (RDBMS) servers 18, such as those for DB2 databases. DB2 databases are known in the art and will not be described further here. The Net.Data gateway 16 and the RDBMS servers 18 may be located in the same server as the web server 14, or the RDBMS servers they may be located on separate machines. The Net.Data gateway 16 facilitates communication between the web clients 12 and the servers 18 executing RDBMS software. The Net.Data gateway 16 enables an application developer to build web applications for the RDBMS software using Structured Query Language (SQL) and either HyperText Markup Language (HTML) or Extensible Markup Language (XML). The application developer creates SQL statements and HTML pages or XML documents and stores them in Net.Data applications, called macro language files, at the Net.Data gateway 16.
An end user of these applications sees only Web pages, such as HTML input forms, on a web browser for his or her requests and the resulting reports. Users fill out the input forms, point and click to navigate the forms, and to access the RDBMS software and relational database. A complete SQL statement is dynamically built by the Net.Data gateway 16 according to the macro language files and/or the user-provided inputs. The SQL statement is sent to the server 18 executing the RDBMS software. The SQL statement is performed by the RDBMS software, and the resulting output is merged into an HTML page or XML document by the Net.Data gateway 16 for presentation to the user.
For example, assume that a macro language file facilitates the retrieval of information concerning various on-line catalog items. A possible SQL statement contained within a Net.Data application is:
SELECT price FROM catalog WHERE itemnum=xe2x80x98$(myitem)xe2x80x99. The xe2x80x9c$(myitem)xe2x80x9d portion of the SQL statement is a Net.Data variable for an item catalog number. The Net.Data gateway 16 receives a value for this Net.Data variable from the end user via an input HTML form. The Net.Data gateway 16 then constructs the SQL statement with this value for $(myitem) according to the macro language file, and sends it to the RDBMS software for execution.
Using this approach, the Net.Data gateway 16 is required to construct a different SQL statement whenever the end user requests access to a different item. The RDBMS software thus must prepare each SQL statement separately. This hinders the performance of the network 10.
Accordingly, there exists a need for an improved method and system for retrieving data from a relational database over a network. The improved method and system should exploit dynamic SQL statement caching to improve performance. The present invention addresses such a need.
The present invention provides a method and system for retrieving data from a database over a network. The present invention includes: generating an executable statement at a gateway in accordance with a macro language file, where the executable statement comprises one or more a parameter markers; passing the executable statement from the gateway to database software; and passing a value for each parameter marker from the gateway to the database software. The present invention allows the Net.Data gateway to support parameter markers in place of Net.Data variables. This in turn allows the Net.Data gateway to take advantage of the dynamic Structured Query Language (SQL) statement caching feature available with conventional database systems. With the use of this invention, the execution of the SQL statements occurs more rapidly, saving resources and improving performance. Additionally, the Net.Data variables may be automatically replaced with parameter markers, with the Net.Data variables and their values maintained. This provides transparent support for parameter markers.